﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using ZLDC.Common.Estate;
using ZLDC.Topo.Model.Estate;

namespace ZLDC.DAL.Estate
{
    public class HousingDAL
    {
        public static void p_housing_save(int Id, int dId, string dname, int aId, string area, string buildName, int buildId, string roomNo, int roomId, int tradeType, int room, int hall, int restroom, int balcony, int kitchen, float acreage, float totalprice, float leaseprice, float unitprice, string owner, string ownerTel, string linkman, string linkmanTel, float costprice, string source, string pocNo, string pocStatus, int redeem, string useStatus, string propertyRight, string direct, string fitment, string houseStyle, int use, string houseType, int hasKey, string keyNo, string keepKey, string keepKeyDate, int ctyCont, string ctyContUser, string ctyContDate, int isOnly, string OnlyUser, string OnlyDate, int markUId, string markUser, int markUDeptId, string markUDept, string remark, string tenant, float assignmentfee, int addUId, string addUser, string addUDept, int addUDeptId, string addUBank, int Protected, int clickCount, string hAppliances, string mark1, string mark2, string mark3, string mark4, string mark5, int status, int status1, string modifyUser, int dltFlag, int isValid, int recommend, int hasPhoto, int buildId1, int buildId2, int buildId3, string EndDate, string ctyEndDate, out string outmsg, out int newid, _Database db)
        {
            List<SqlParameter> parms = new List<SqlParameter>();

            parms.Add(new SqlParameter("@Id", Id));
            parms.Add(new SqlParameter("@dId", dId));
            parms.Add(new SqlParameter("@dname", dname));
            parms.Add(new SqlParameter("@aId", aId));
            parms.Add(new SqlParameter("@area", area));
            parms.Add(new SqlParameter("@buildName", buildName));
            parms.Add(new SqlParameter("@buildId", buildId));
            parms.Add(new SqlParameter("@roomNo", roomNo));
            parms.Add(new SqlParameter("@roomId", roomId));
            parms.Add(new SqlParameter("@tradeType", tradeType));
            parms.Add(new SqlParameter("@room", room));
            parms.Add(new SqlParameter("@hall", hall));
            parms.Add(new SqlParameter("@restroom", restroom));
            parms.Add(new SqlParameter("@balcony", balcony));
            parms.Add(new SqlParameter("@kitchen", kitchen));
            parms.Add(new SqlParameter("@acreage", acreage));
            parms.Add(new SqlParameter("@totalprice", totalprice));
            parms.Add(new SqlParameter("@leaseprice", leaseprice));
            parms.Add(new SqlParameter("@unitprice", unitprice));
            parms.Add(new SqlParameter("@owner", owner));
            parms.Add(new SqlParameter("@ownerTel", ownerTel));
            parms.Add(new SqlParameter("@linkman", linkman));
            parms.Add(new SqlParameter("@linkmanTel", linkmanTel));
            parms.Add(new SqlParameter("@costprice", costprice));
            parms.Add(new SqlParameter("@source", source));
            parms.Add(new SqlParameter("@pocNo", pocNo));
            parms.Add(new SqlParameter("@pocStatus", pocStatus));
            parms.Add(new SqlParameter("@redeem", redeem));
            parms.Add(new SqlParameter("@useStatus", useStatus));
            parms.Add(new SqlParameter("@propertyRight", propertyRight));
            parms.Add(new SqlParameter("@direct", direct));
            parms.Add(new SqlParameter("@fitment", fitment));
            parms.Add(new SqlParameter("@houseStyle", houseStyle));
            parms.Add(new SqlParameter("@use", use));
            parms.Add(new SqlParameter("@houseType", houseType));
            parms.Add(new SqlParameter("@hasKey", hasKey));
            parms.Add(new SqlParameter("@keyNo", keyNo));
            parms.Add(new SqlParameter("@keepKey", keepKey));
            parms.Add(new SqlParameter("@keepKeyDate", keepKeyDate));
            parms.Add(new SqlParameter("@ctyCont", ctyCont));
            parms.Add(new SqlParameter("@ctyContUser", ctyContUser));
            parms.Add(new SqlParameter("@ctyContDate", ctyContDate));
            parms.Add(new SqlParameter("@isOnly", isOnly));
            parms.Add(new SqlParameter("@OnlyUser", OnlyUser));
            parms.Add(new SqlParameter("@OnlyDate", OnlyDate));
            parms.Add(new SqlParameter("@markUId", markUId));
            parms.Add(new SqlParameter("@markUser", markUser));
            parms.Add(new SqlParameter("@markUDeptId", markUDeptId));
            parms.Add(new SqlParameter("@markUDept", markUDept));
            parms.Add(new SqlParameter("@remark", remark));
            parms.Add(new SqlParameter("@tenant", tenant));
            parms.Add(new SqlParameter("@assignmentfee", assignmentfee));
            parms.Add(new SqlParameter("@addUId", addUId));
            parms.Add(new SqlParameter("@addUser", addUser));
            parms.Add(new SqlParameter("@addUDept", addUDept));
            parms.Add(new SqlParameter("@addUDeptId", addUDeptId));
            parms.Add(new SqlParameter("@addUBank", addUBank));
            parms.Add(new SqlParameter("@Protected", Protected));
            parms.Add(new SqlParameter("@clickCount", clickCount));
            parms.Add(new SqlParameter("@hAppliances", hAppliances));
            parms.Add(new SqlParameter("@mark1", mark1));
            parms.Add(new SqlParameter("@mark2", mark2));
            parms.Add(new SqlParameter("@mark3", mark3));
            parms.Add(new SqlParameter("@mark4", mark4));
            parms.Add(new SqlParameter("@mark5", mark5));
            parms.Add(new SqlParameter("@status", status));
            parms.Add(new SqlParameter("@status1", status1));
            parms.Add(new SqlParameter("@modifyUser", modifyUser));
            parms.Add(new SqlParameter("@dltFlag", dltFlag));
            parms.Add(new SqlParameter("@isValid", isValid));
            parms.Add(new SqlParameter("@recommend", recommend));
            parms.Add(new SqlParameter("@hasPhoto", hasPhoto));
            parms.Add(new SqlParameter("@level1", buildId1));
            parms.Add(new SqlParameter("@level2", buildId2));
            parms.Add(new SqlParameter("@level3", buildId3));
            //parms.Add(new SqlParameter("@EndDate", EndDate));
            //parms.Add(new SqlParameter("@ctyEndDate", ctyEndDate));


            SqlParameter pOutmsg = new SqlParameter("@outmsg", SqlDbType.VarChar, 256);
            pOutmsg.Direction = ParameterDirection.Output;
            parms.Add(pOutmsg);

            SqlParameter pNewid = new SqlParameter("@newid", SqlDbType.Int, 4);
            pNewid.Direction = ParameterDirection.Output;
            parms.Add(pNewid);
            SqlParameter pRetval = new SqlParameter("@retval", SqlDbType.Int, 4);
            pRetval.Direction = ParameterDirection.ReturnValue;
            parms.Add(pRetval);


            db.execute_procedure("p_housing_save", parms);

            outmsg = (pOutmsg.Value == System.DBNull.Value) ? "" : Convert.ToString(pOutmsg.Value);
            newid = (pNewid.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pNewid.Value);

            int retval = (pRetval.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pRetval.Value);

        }

        public static void p_changehousing_save(int Id, int dId, string dname, int aId, string area, string buildName, int buildId, string roomNo, int roomId, int tradeType, int room, int hall, int restroom, int balcony, int kitchen, float acreage, float totalprice, float leaseprice, float unitprice, string owner, string ownerTel, string linkman, string linkmanTel, float costprice, string source, string pocNo, string pocStatus, int redeem, string useStatus, string propertyRight, string direct, string fitment, string houseStyle, int use, string houseType, int hasKey, string keyNo, string keepKey, string keepKeyDate, int ctyCont, string ctyContUser, string ctyContDate, int isOnly, string OnlyUser, string OnlyDate, int markUId, string markUser, int markUDeptId, string markUDept, string remark, string tenant, float assignmentfee, int addUId, string addUser, string addUDept, int addUDeptId, string addUBank, int Protected, int clickCount, string hAppliances, string mark1, string mark2, string mark3, string mark4, string mark5, int status, int status1, string modifyUser, int dltFlag, int isValid, int recommend, int hasPhoto, int buildId1, int buildId2, int buildId3, string EndDate, string ctyEndDate, out string outmsg, out int newid, _Database db)
        {
            List<SqlParameter> parms = new List<SqlParameter>();

            parms.Add(new SqlParameter("@Id", Id));
            parms.Add(new SqlParameter("@dId", dId));
            parms.Add(new SqlParameter("@dname", dname));
            parms.Add(new SqlParameter("@aId", aId));
            parms.Add(new SqlParameter("@area", area));
            parms.Add(new SqlParameter("@buildName", buildName));
            parms.Add(new SqlParameter("@buildId", buildId));
            parms.Add(new SqlParameter("@roomNo", roomNo));
            parms.Add(new SqlParameter("@roomId", roomId));
            parms.Add(new SqlParameter("@tradeType", tradeType));
            parms.Add(new SqlParameter("@room", room));
            parms.Add(new SqlParameter("@hall", hall));
            parms.Add(new SqlParameter("@restroom", restroom));
            parms.Add(new SqlParameter("@balcony", balcony));
            parms.Add(new SqlParameter("@kitchen", kitchen));
            parms.Add(new SqlParameter("@acreage", acreage));
            parms.Add(new SqlParameter("@totalprice", totalprice));
            parms.Add(new SqlParameter("@leaseprice", leaseprice));
            parms.Add(new SqlParameter("@unitprice", unitprice));
            parms.Add(new SqlParameter("@owner", owner));
            parms.Add(new SqlParameter("@ownerTel", ownerTel));
            parms.Add(new SqlParameter("@linkman", linkman));
            parms.Add(new SqlParameter("@linkmanTel", linkmanTel));
            parms.Add(new SqlParameter("@costprice", costprice));
            parms.Add(new SqlParameter("@source", source));
            parms.Add(new SqlParameter("@pocNo", pocNo));
            parms.Add(new SqlParameter("@pocStatus", pocStatus));
            parms.Add(new SqlParameter("@redeem", redeem));
            parms.Add(new SqlParameter("@useStatus", useStatus));
            parms.Add(new SqlParameter("@propertyRight", propertyRight));
            parms.Add(new SqlParameter("@direct", direct));
            parms.Add(new SqlParameter("@fitment", fitment));
            parms.Add(new SqlParameter("@houseStyle", houseStyle));
            parms.Add(new SqlParameter("@use", use));
            parms.Add(new SqlParameter("@houseType", houseType));
            parms.Add(new SqlParameter("@hasKey", hasKey));
            parms.Add(new SqlParameter("@keyNo", keyNo));
            parms.Add(new SqlParameter("@keepKey", keepKey));
            parms.Add(new SqlParameter("@keepKeyDate", keepKeyDate));
            parms.Add(new SqlParameter("@ctyCont", ctyCont));
            parms.Add(new SqlParameter("@ctyContUser", ctyContUser));
            parms.Add(new SqlParameter("@ctyContDate", ctyContDate));
            parms.Add(new SqlParameter("@isOnly", isOnly));
            parms.Add(new SqlParameter("@OnlyUser", OnlyUser));
            parms.Add(new SqlParameter("@OnlyDate", OnlyDate));
            parms.Add(new SqlParameter("@markUId", markUId));
            parms.Add(new SqlParameter("@markUser", markUser));
            parms.Add(new SqlParameter("@markUDeptId", markUDeptId));
            parms.Add(new SqlParameter("@markUDept", markUDept));
            parms.Add(new SqlParameter("@remark", remark));
            parms.Add(new SqlParameter("@tenant", tenant));
            parms.Add(new SqlParameter("@assignmentfee", assignmentfee));
            parms.Add(new SqlParameter("@addUId", addUId));
            parms.Add(new SqlParameter("@addUser", addUser));
            parms.Add(new SqlParameter("@addUDept", addUDept));
            parms.Add(new SqlParameter("@addUDeptId", addUDeptId));
            parms.Add(new SqlParameter("@addUBank", addUBank));
            parms.Add(new SqlParameter("@Protected", Protected));
            parms.Add(new SqlParameter("@clickCount", clickCount));
            parms.Add(new SqlParameter("@hAppliances", hAppliances));
            parms.Add(new SqlParameter("@mark1", mark1));
            parms.Add(new SqlParameter("@mark2", mark2));
            parms.Add(new SqlParameter("@mark3", mark3));
            parms.Add(new SqlParameter("@mark4", mark4));
            parms.Add(new SqlParameter("@mark5", mark5));
            parms.Add(new SqlParameter("@status", status));
            parms.Add(new SqlParameter("@status1", status1));
            parms.Add(new SqlParameter("@modifyUser", modifyUser));
            parms.Add(new SqlParameter("@dltFlag", dltFlag));
            parms.Add(new SqlParameter("@isValid", isValid));
            parms.Add(new SqlParameter("@recommend", recommend));
            parms.Add(new SqlParameter("@hasPhoto", hasPhoto));
            parms.Add(new SqlParameter("@level1", buildId1));
            parms.Add(new SqlParameter("@level2", buildId2));
            parms.Add(new SqlParameter("@level3", buildId3));
            parms.Add(new SqlParameter("@EndDate", EndDate));
            parms.Add(new SqlParameter("@ctyEndDate", ctyEndDate));

            SqlParameter pOutmsg = new SqlParameter("@outmsg", SqlDbType.VarChar, 256);
            pOutmsg.Direction = ParameterDirection.Output;
            parms.Add(pOutmsg);

            SqlParameter pNewid = new SqlParameter("@newid", SqlDbType.Int, 4);
            pNewid.Direction = ParameterDirection.Output;
            parms.Add(pNewid);
            SqlParameter pRetval = new SqlParameter("@retval", SqlDbType.Int, 4);
            pRetval.Direction = ParameterDirection.ReturnValue;
            parms.Add(pRetval);


            db.execute_procedure("p_changehousing_save", parms);

            outmsg = (pOutmsg.Value == System.DBNull.Value) ? "" : Convert.ToString(pOutmsg.Value);
            newid = (pNewid.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pNewid.Value);

            int retval = (pRetval.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pRetval.Value);

        }

        public static DataTable p_housing_list_table(int userid, int page, int pagesize, string condition, string orderByName, string orderByType, ref int total, _Database db)
        {
            List<SqlParameter> parms = new List<SqlParameter>();

            parms.Add(new SqlParameter("@userid", userid));
            parms.Add(new SqlParameter("@page", page));
            parms.Add(new SqlParameter("@pagesize", pagesize));
            parms.Add(new SqlParameter("@condition", condition));
            parms.Add(new SqlParameter("@orderByName", orderByName));
            parms.Add(new SqlParameter("@orderByType", orderByType));

            SqlParameter pTotal = new SqlParameter("@total", SqlDbType.Int, 4);
            pTotal.Direction = ParameterDirection.InputOutput;
            pTotal.Value = total;
            parms.Add(pTotal);
            SqlParameter pRetval = new SqlParameter("@retval", SqlDbType.Int, 4);
            pRetval.Direction = ParameterDirection.ReturnValue;
            parms.Add(pRetval);

            DataTable dt = null;
            dt = db.GetDataTable("p_housing_list", parms);

            total = (pTotal.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pTotal.Value);

            int retval = (pRetval.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pRetval.Value);

            return dt;
        }

        public static DataTable p_housing_list_action_table(int userid, int page, int pagesize, string condition, string orderByName, string orderByType, ref int total, _Database db)
        {
            List<SqlParameter> parms = new List<SqlParameter>();

            parms.Add(new SqlParameter("@userid", userid));
            parms.Add(new SqlParameter("@page", page));
            parms.Add(new SqlParameter("@pagesize", pagesize));
            parms.Add(new SqlParameter("@condition", condition));
            parms.Add(new SqlParameter("@orderByName", orderByName));
            parms.Add(new SqlParameter("@orderByType", orderByType));

            SqlParameter pTotal = new SqlParameter("@total", SqlDbType.Int, 4);
            pTotal.Direction = ParameterDirection.InputOutput;
            pTotal.Value = total;
            parms.Add(pTotal);
            SqlParameter pRetval = new SqlParameter("@retval", SqlDbType.Int, 4);
            pRetval.Direction = ParameterDirection.ReturnValue;
            parms.Add(pRetval);

            DataTable dt = null;
            dt = db.GetDataTable("p_housing_list_action", parms);

            total = (pTotal.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pTotal.Value);

            int retval = (pRetval.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pRetval.Value);

            return dt;
        }

        public static DataTable p_housing_list_check_table(int userid, int page, int pagesize, string condition, string orderByName, string orderByType, ref int total, _Database db)
        {
            List<SqlParameter> parms = new List<SqlParameter>();

            parms.Add(new SqlParameter("@userid", userid));
            parms.Add(new SqlParameter("@page", page));
            parms.Add(new SqlParameter("@pagesize", pagesize));
            parms.Add(new SqlParameter("@condition", condition));
            parms.Add(new SqlParameter("@orderByName", orderByName));
            parms.Add(new SqlParameter("@orderByType", orderByType));

            SqlParameter pTotal = new SqlParameter("@total", SqlDbType.Int, 4);
            pTotal.Direction = ParameterDirection.InputOutput;
            pTotal.Value = total;
            parms.Add(pTotal);
            SqlParameter pRetval = new SqlParameter("@retval", SqlDbType.Int, 4);
            pRetval.Direction = ParameterDirection.ReturnValue;
            parms.Add(pRetval);

            DataTable dt = null;
            dt = db.GetDataTable("p_housing_list_check", parms);

            total = (pTotal.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pTotal.Value);

            int retval = (pRetval.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pRetval.Value);

            return dt;
        }

        public static DataTable p_housing_list_new_table(int userid, int page, int pagesize, string condition, string orderByName, string orderByType, ref int total, _Database db)
        {
            List<SqlParameter> parms = new List<SqlParameter>();

            parms.Add(new SqlParameter("@userid", userid));
            parms.Add(new SqlParameter("@page", page));
            parms.Add(new SqlParameter("@pagesize", pagesize));
            parms.Add(new SqlParameter("@condition", condition));
            parms.Add(new SqlParameter("@orderByName", orderByName));
            parms.Add(new SqlParameter("@orderByType", orderByType));

            SqlParameter pTotal = new SqlParameter("@total", SqlDbType.Int, 4);
            pTotal.Direction = ParameterDirection.InputOutput;
            pTotal.Value = total;
            parms.Add(pTotal);
            SqlParameter pRetval = new SqlParameter("@retval", SqlDbType.Int, 4);
            pRetval.Direction = ParameterDirection.ReturnValue;
            parms.Add(pRetval);

            DataTable dt = null;
            dt = db.GetDataTable("p_housing_list_new", parms);

            total = (pTotal.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pTotal.Value);

            int retval = (pRetval.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pRetval.Value);

            return dt;
        }

        public static DataTable p_housing_list_favorite_table(int page, int pagesize, string condition, string orderByName, string orderByType, ref int total, _Database db)
        {
            List<SqlParameter> parms = new List<SqlParameter>();

            parms.Add(new SqlParameter("@page", page));
            parms.Add(new SqlParameter("@pagesize", pagesize));
            parms.Add(new SqlParameter("@condition", condition));
            parms.Add(new SqlParameter("@orderByName", orderByName));
            parms.Add(new SqlParameter("@orderByType", orderByType));

            SqlParameter pTotal = new SqlParameter("@total", SqlDbType.Int, 4);
            pTotal.Direction = ParameterDirection.InputOutput;
            pTotal.Value = total;
            parms.Add(pTotal);
            SqlParameter pRetval = new SqlParameter("@retval", SqlDbType.Int, 4);
            pRetval.Direction = ParameterDirection.ReturnValue;
            parms.Add(pRetval);

            DataTable dt = null;
            dt = db.GetDataTable("p_housing_list_favorite", parms);

            total = (pTotal.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pTotal.Value);

            int retval = (pRetval.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pRetval.Value);

            return dt;
        }

        public static DataTable p_housing_list_my_table(int userid, int page, int pagesize, string condition, string orderByName, string orderByType, ref int total, _Database db)
        {
            List<SqlParameter> parms = new List<SqlParameter>();

            parms.Add(new SqlParameter("@userid", userid));
            parms.Add(new SqlParameter("@page", page));
            parms.Add(new SqlParameter("@pagesize", pagesize));
            parms.Add(new SqlParameter("@condition", condition));
            parms.Add(new SqlParameter("@orderByName", orderByName));
            parms.Add(new SqlParameter("@orderByType", orderByType));

            SqlParameter pTotal = new SqlParameter("@total", SqlDbType.Int, 4);
            pTotal.Direction = ParameterDirection.InputOutput;
            pTotal.Value = total;
            parms.Add(pTotal);
            SqlParameter pRetval = new SqlParameter("@retval", SqlDbType.Int, 4);
            pRetval.Direction = ParameterDirection.ReturnValue;
            parms.Add(pRetval);

            DataTable dt = null;
            dt = db.GetDataTable("p_housing_list_my", parms);

            total = (pTotal.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pTotal.Value);

            int retval = (pRetval.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pRetval.Value);

            return dt;
        }

        public static int HousingDelete(int id, int roomdId, int tradeType, _Database db)
        {
            int rows = 0;

            try
            {
                string sql = "update t_housing set dltFlag = 1,modifydate=getDate(),modifyuser='" + _Utility.GetInfoBySession().m_sUserName + "' where id=" + id + ";";
                sql += "declare @TradType int " +
                     "declare @roomId int " +
                     "select @TradType=tradeType From t_housing where roomId=" + roomdId + " and tradeType!=" + tradeType + " and dltflag=0  " +
                     "if(@TradType=1) " +
                     "begin " +
                     "update t_roomno set hid='',bstatus=@TradType where id=" + roomdId + " " +
                     "end " +
                     "else if(@TradType=2) " +
                     "begin " +
                     "update t_roomno set hid1='',bstatus=@TradType where id=" + roomdId + " " +
                     "end " +
                     "else " +
                     "begin " +
                     "update t_roomno set hid='',hid1='',bstatus=0 where id=" + roomdId + " " +
                     "end";
                rows = db.ExecuteSql(sql);
            }
            catch { }
            finally { db.close(); }

            return rows;
        }

        public static int HousingRecommend(int id, int isrecommend, _Database db)
        {
            int rows = 0;

            try
            {
                string sql = "update t_housing set recommend = " + isrecommend + " where id=" + id + "";
                rows = db.ExecuteSql(sql);
            }
            catch { }
            finally { db.close(); }

            return rows;
        }

        public static int HousingCheck(int id, int status, _Database db)
        {
            int rows = 0;
            string sql = "";
            try
            {

                if (status < 2)
                    sql = "update t_housing set isValid = " + status + " where id=" + id + "";
                else
                    sql = "update t_housing set status=3,isValid =0 where id=" + id + "";
                rows = db.ExecuteSql(sql);
            }
            catch { }
            finally { db.close(); }

            return rows;
        }

        public static int HousingCheckStatus1(int id, int status1, _Database db)
        {
            int rows = 0;

            try
            {
                string sql = "";

                if (status1 == 1)
                {
                    sql += "update t_housing set status = 1,status1=0,markDate=GETDATE() where id=" + id + "; ";

                }
                else
                {
                    sql += "update t_housing set status1=0,markDate=GETDATE() where id=" + id + "";
                }
                rows = db.ExecuteSql(sql);
            }
            catch { }
            finally { db.close(); }

            return rows;
        }

        public static int HousingLock(int id, int Protected, int protectedUid, string protectedUser, int protectedUDeptId, string protectedUDept, _Database db)
        {
            int rows = 0;

            try
            {
                string sql = "update t_housing set Protected = " + Protected + ", ProtectedDate=GETDATE(), protectedUid=" + protectedUid + ", protectedUser='" + protectedUser
                    + "',protectedUDeptId=" + protectedUDeptId + ", protectedUDept='" + protectedUDept + "' where id=" + id + "";
                rows = db.ExecuteSql(sql);
            }
            catch { }
            finally { db.close(); }

            return rows;
        }


        public static int HousingStatus(int id, int status, _Database db)
        {
            int rows = 0;

            try
            {
                string sql = "update t_housing set status = " + status + ",modifyDate=GETDATE(),markDate=GETDATE() where id=" + id + "";
                rows = db.ExecuteSql(sql);
            }
            catch { }
            finally { db.close(); }

            return rows;
        }

        public static int HousingStatus(int id, int markUid, string markUser, int markUdeptid, string markUdept, _Database db)
        {
            int rows = 0;

            try
            {
                string sql = "update t_housing set status = 1, markUId=" + markUid + ",markUDept='" + markUdept + "',markUser='" + markUser + "',markUDeptId=" + markUdeptid + ", markDate='" + DateTime.Now.ToString() + "' where id=" + id + ";";
                sql += " update t_housing set hasKey=0,keyNo='',keepKey='',keepKeyDate='',ctyCont=0,ctyContUser='',ctyContDate='',isOnly=0,OnlyUser='',OnlyDate='' where Id=" + id + ""; rows = db.ExecuteSql(sql);
            }
            catch { }
            finally { db.close(); }

            return rows;
        }

        public static int UpdateStatus(string id, int deptid_from, string deptname_from, int userid_from, string username_from, string flag, int userid_to, int deptid_to, string deptname_to, string username_to, _Database db)
        {
            int rows = 0;

            try
            {
                string sql = "";
                string sqlStr = "";
                if (flag == "true")
                {
                    sqlStr = "SELECT [idlist]=STUFF((SELECT ','+cast([id] as varchar) FROM t_housing t where markUId =" + userid_from + " FOR XML PATH('')), 1, 1, '')";

                    sql = "update t_housing set markUId=" + userid_to + ",markUDept='" + deptname_to + "',markUser='" + username_to + "',markUDeptId=" + deptid_to + " where markuid =" + userid_from + " ";

                }
                else
                {
                    sql = "update t_housing set markUId=" + userid_to + ",markUDept='" + deptname_to + "',markUser='" + username_to + "',markUDeptId=" + deptid_to + " where Id in (" + id + ")";
                }


                DataSet ds = null;
                string IdList = "";

                if (sqlStr != "")
                {
                    ds = db.Query(sqlStr);

                    db.Connection.Open();

                    if (ds.Tables[0].Rows.Count > 0)
                    {
                        IdList = ds.Tables[0].Rows[0][0].ToString();
                    }
                }
                if (IdList == "")
                {
                    IdList = id;
                }
                rows = db.ExecuteSql(sql);


                if (rows > 0)
                {
                    string outmsg = "";
                    int newid = 0;
                    int user_id = _Convert.ToInt(_Utility.GetInfoBySession().m_sUserID); string user_name = _Utility.GetInfoBySession().m_sUserName; int user_from = userid_from; string user_from_name = username_from; int user_to = userid_to; string user_to_name = username_to; string opr_type = "转盘"; string sqlText = sql + "detail:(" + IdList + ")";
                    ZLDC.DAL.Estate.TranLogDAL.p_tran_log_insert(user_id, user_name, user_from, user_from_name, user_to, user_to_name, opr_type, sqlText, out outmsg, out newid, db);


                }
            }
            catch { }
            finally { db.close(); }

            return rows;
        }




        public static void p_housing_change(int id, int status1, int userId, string userName, int deptid, string deptName, string content, string important, out string outmsg, _Database db)
        {
            List<SqlParameter> parms = new List<SqlParameter>();

            parms.Add(new SqlParameter("@id", id));
            parms.Add(new SqlParameter("@status1", status1));
            parms.Add(new SqlParameter("@userId", userId));
            parms.Add(new SqlParameter("@userName", userName));
            parms.Add(new SqlParameter("@deptid", deptid));
            parms.Add(new SqlParameter("@deptName", deptName));
            parms.Add(new SqlParameter("@content", content));
            parms.Add(new SqlParameter("@important", important));
            parms.Add(new SqlParameter("@fdate", DateTime.Now));


            SqlParameter pOutmsg = new SqlParameter("@outmsg", SqlDbType.VarChar, 256);
            pOutmsg.Direction = ParameterDirection.Output;
            parms.Add(pOutmsg);
            SqlParameter pRetval = new SqlParameter("@retval", SqlDbType.Int, 4);
            pRetval.Direction = ParameterDirection.ReturnValue;
            parms.Add(pRetval);


            db.execute_procedure("p_housing_change", parms);

            outmsg = (pOutmsg.Value == System.DBNull.Value) ? "" : Convert.ToString(pOutmsg.Value);

            int retval = (pRetval.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pRetval.Value);

        }

        public static DataTable p_housing_detail_table(int Id, _Database db)
        {
            List<SqlParameter> parms = new List<SqlParameter>();

            parms.Add(new SqlParameter("@Id", Id));
            SqlParameter pRetval = new SqlParameter("@retval", SqlDbType.Int, 4);
            pRetval.Direction = ParameterDirection.ReturnValue;
            parms.Add(pRetval);

            DataTable dt = null;
            dt = db.GetDataTable("p_housing_detail", parms);

            int retval = (pRetval.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pRetval.Value);

            return dt;
        }

        public static HousingInfo p_housing_detail(int Id, _Database db)
        {
            List<SqlParameter> parms = new List<SqlParameter>();

            parms.Add(new SqlParameter("@Id", Id));
            SqlParameter pRetval = new SqlParameter("@retval", SqlDbType.Int, 4);
            pRetval.Direction = ParameterDirection.ReturnValue;
            parms.Add(pRetval);


            db.fetch_procedure("p_housing_detail", parms);

            int retval = (pRetval.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pRetval.Value);

            if (db.num_rows == 0)
            {
                return null;
            }
            else
            {
                HousingInfo c = new HousingInfo();
                c.TradeType = (db.rows[0]["tradeType"] == System.DBNull.Value) ? 0 : Convert.ToInt32(db.rows[0]["tradeType"].ToString());
                c.Redeem = (db.rows[0]["redeem"] == System.DBNull.Value) ? 0 : Convert.ToInt32(db.rows[0]["redeem"].ToString());
                c.HasKey = (db.rows[0]["hasKey"] == System.DBNull.Value) ? 0 : Convert.ToInt32(db.rows[0]["hasKey"].ToString());
                c.CtyCont = (db.rows[0]["ctyCont"] == System.DBNull.Value) ? 0 : Convert.ToInt32(db.rows[0]["ctyCont"].ToString());
                c.IsOnly = (db.rows[0]["isOnly"] == System.DBNull.Value) ? 0 : Convert.ToInt32(db.rows[0]["isOnly"].ToString());
                c.Protected = (db.rows[0]["Protected"] == System.DBNull.Value) ? 0 : Convert.ToInt32(db.rows[0]["Protected"].ToString());
                c.Status = (db.rows[0]["status"] == System.DBNull.Value) ? 0 : Convert.ToInt32(db.rows[0]["status"].ToString());
                c.Status1 = (db.rows[0]["status1"] == System.DBNull.Value) ? 0 : Convert.ToInt32(db.rows[0]["status1"].ToString());
                c.DltFlag = (db.rows[0]["dltFlag"] == System.DBNull.Value) ? 0 : Convert.ToInt32(db.rows[0]["dltFlag"].ToString());
                c.IsValid = (db.rows[0]["isValid"] == System.DBNull.Value) ? 0 : Convert.ToInt32(db.rows[0]["isValid"].ToString());
                c.Recommend = (db.rows[0]["recommend"] == System.DBNull.Value) ? 0 : Convert.ToInt32(db.rows[0]["recommend"].ToString());
                c.HasPhoto = (db.rows[0]["hasPhoto"] == System.DBNull.Value) ? 0 : Convert.ToInt32(db.rows[0]["hasPhoto"].ToString());
                c.Id = (db.rows[0]["Id"] == System.DBNull.Value) ? 0 : Convert.ToInt32(db.rows[0]["Id"].ToString());
                c.DId = (db.rows[0]["dId"] == System.DBNull.Value) ? 0 : Convert.ToInt32(db.rows[0]["dId"].ToString());
                c.AId = (db.rows[0]["aId"] == System.DBNull.Value) ? 0 : Convert.ToInt32(db.rows[0]["aId"].ToString());
                c.BuildId = (db.rows[0]["buildId"] == System.DBNull.Value) ? 0 : Convert.ToInt32(db.rows[0]["buildId"].ToString());
                c.RoomId = (db.rows[0]["roomId"] == System.DBNull.Value) ? 0 : Convert.ToInt32(db.rows[0]["roomId"].ToString());
                c.Room = (db.rows[0]["room"] == System.DBNull.Value) ? 0 : Convert.ToInt32(db.rows[0]["room"].ToString());
                c.AddUId = (db.rows[0]["addUId"] == System.DBNull.Value) ? 0 : Convert.ToInt32(db.rows[0]["addUId"].ToString());
                c.AddUDeptId = (db.rows[0]["addUDeptId"] == System.DBNull.Value) ? 0 : Convert.ToInt32(db.rows[0]["addUDeptId"].ToString());
                c.ClickCount = (db.rows[0]["clickCount"] == System.DBNull.Value) ? 0 : Convert.ToInt32(db.rows[0]["clickCount"].ToString());
                c.Hall = (db.rows[0]["hall"] == System.DBNull.Value) ? 0 : Convert.ToInt32(db.rows[0]["hall"].ToString());
                c.Restroom = (db.rows[0]["restroom"] == System.DBNull.Value) ? 0 : Convert.ToInt32(db.rows[0]["restroom"].ToString());
                c.Balcony = (db.rows[0]["balcony"] == System.DBNull.Value) ? 0 : Convert.ToInt32(db.rows[0]["balcony"].ToString());
                c.Kitchen = (db.rows[0]["kitchen"] == System.DBNull.Value) ? 0 : Convert.ToInt32(db.rows[0]["kitchen"].ToString());
                c.MarkUId = (db.rows[0]["markUId"] == System.DBNull.Value) ? 0 : Convert.ToInt32(db.rows[0]["markUId"].ToString());
                c.MarkUDeptId = (db.rows[0]["markUDeptId"] == System.DBNull.Value) ? 0 : Convert.ToInt32(db.rows[0]["markUDeptId"].ToString());
                c.UDDate = (db.rows[0]["UDDate"] == System.DBNull.Value) ? DateTime.MinValue : Convert.ToDateTime(db.rows[0]["UDDate"].ToString());
                c.MarkDate = (db.rows[0]["markDate"] == System.DBNull.Value) ? DateTime.MinValue : Convert.ToDateTime(db.rows[0]["markDate"].ToString());
                c.AddDate = (db.rows[0]["addDate"] == System.DBNull.Value) ? DateTime.MinValue : Convert.ToDateTime(db.rows[0]["addDate"].ToString());
                c.ModifyDate = (db.rows[0]["modifyDate"] == System.DBNull.Value) ? DateTime.MinValue : Convert.ToDateTime(db.rows[0]["modifyDate"].ToString());
                c.ProtectedDate = (db.rows[0]["ProtectedDate"] == System.DBNull.Value) ? DateTime.MinValue : Convert.ToDateTime(db.rows[0]["ProtectedDate"].ToString());
                c.Assignmentfee = (db.rows[0]["assignmentfee"] == System.DBNull.Value) ? 0 : Convert.ToSingle(db.rows[0]["assignmentfee"].ToString());
                c.Acreage = (db.rows[0]["acreage"] == System.DBNull.Value) ? 0 : Convert.ToSingle(db.rows[0]["acreage"].ToString());
                c.Totalprice = (db.rows[0]["totalprice"] == System.DBNull.Value) ? 0 : Convert.ToSingle(db.rows[0]["totalprice"].ToString());
                c.Leaseprice = (db.rows[0]["leaseprice"] == System.DBNull.Value) ? 0 : Convert.ToSingle(db.rows[0]["leaseprice"].ToString());
                c.Unitprice = (db.rows[0]["unitprice"] == System.DBNull.Value) ? 0 : Convert.ToSingle(db.rows[0]["unitprice"].ToString());
                c.Costprice = (db.rows[0]["costprice"] == System.DBNull.Value) ? 0 : Convert.ToSingle(db.rows[0]["costprice"].ToString());
                c.Dname = (db.rows[0]["dname"] == System.DBNull.Value) ? "" : db.rows[0]["dname"].ToString();
                c.Area = (db.rows[0]["area"] == System.DBNull.Value) ? "" : db.rows[0]["area"].ToString();
                c.BuildName = (db.rows[0]["buildName"] == System.DBNull.Value) ? "" : db.rows[0]["buildName"].ToString();
                c.RoomNo = (db.rows[0]["roomNo"] == System.DBNull.Value) ? "" : db.rows[0]["roomNo"].ToString();
                c.Owner = (db.rows[0]["owner"] == System.DBNull.Value) ? "" : db.rows[0]["owner"].ToString();
                c.Linkman = (db.rows[0]["linkman"] == System.DBNull.Value) ? "" : db.rows[0]["linkman"].ToString();
                c.Mark5 = (db.rows[0]["mark5"] == System.DBNull.Value) ? "" : db.rows[0]["mark5"].ToString();
                c.ModifyUser = (db.rows[0]["modifyUser"] == System.DBNull.Value) ? "" : db.rows[0]["modifyUser"].ToString();
                c.AddUBank = (db.rows[0]["addUBank"] == System.DBNull.Value) ? "" : db.rows[0]["addUBank"].ToString();
                c.HAppliances = (db.rows[0]["hAppliances"] == System.DBNull.Value) ? "" : db.rows[0]["hAppliances"].ToString();
                c.Mark1 = (db.rows[0]["mark1"] == System.DBNull.Value) ? "" : db.rows[0]["mark1"].ToString();
                c.Mark2 = (db.rows[0]["mark2"] == System.DBNull.Value) ? "" : db.rows[0]["mark2"].ToString();
                c.Mark3 = (db.rows[0]["mark3"] == System.DBNull.Value) ? "" : db.rows[0]["mark3"].ToString();
                c.Mark4 = (db.rows[0]["mark4"] == System.DBNull.Value) ? "" : db.rows[0]["mark4"].ToString();
                c.MarkUser = (db.rows[0]["markUser"] == System.DBNull.Value) ? "" : db.rows[0]["markUser"].ToString();
                c.MarkUDept = (db.rows[0]["markUDept"] == System.DBNull.Value) ? "" : db.rows[0]["markUDept"].ToString();
                c.Remark = (db.rows[0]["remark"] == System.DBNull.Value) ? "" : db.rows[0]["remark"].ToString();
                c.Tenant = (db.rows[0]["tenant"] == System.DBNull.Value) ? "" : db.rows[0]["tenant"].ToString();
                c.AddUser = (db.rows[0]["addUser"] == System.DBNull.Value) ? "" : db.rows[0]["addUser"].ToString();
                c.AddUDept = (db.rows[0]["addUDept"] == System.DBNull.Value) ? "" : db.rows[0]["addUDept"].ToString();
                c.KeepKey = (db.rows[0]["keepKey"] == System.DBNull.Value) ? "" : db.rows[0]["keepKey"].ToString();
                c.KeepKeyDate = (db.rows[0]["keepKeyDate"] == System.DBNull.Value) ? "" : db.rows[0]["keepKeyDate"].ToString();
                c.CtyContUser = (db.rows[0]["ctyContUser"] == System.DBNull.Value) ? "" : db.rows[0]["ctyContUser"].ToString();
                c.CtyContDate = (db.rows[0]["ctyContDate"] == System.DBNull.Value) ? "" : db.rows[0]["ctyContDate"].ToString();
                c.OnlyUser = (db.rows[0]["OnlyUser"] == System.DBNull.Value) ? "" : db.rows[0]["OnlyUser"].ToString();
                c.OnlyDate = (db.rows[0]["OnlyDate"] == System.DBNull.Value) ? "" : db.rows[0]["OnlyDate"].ToString();
                c.Direct = (db.rows[0]["direct"] == System.DBNull.Value) ? "" : db.rows[0]["direct"].ToString();
                c.Fitment = (db.rows[0]["fitment"] == System.DBNull.Value) ? "" : db.rows[0]["fitment"].ToString();
                c.HouseStyle = (db.rows[0]["houseStyle"] == System.DBNull.Value) ? "" : db.rows[0]["houseStyle"].ToString();
                c.Use = (db.rows[0]["use"] == System.DBNull.Value) ? "" : db.rows[0]["use"].ToString();
                c.HouseType = (db.rows[0]["houseType"] == System.DBNull.Value) ? "" : db.rows[0]["houseType"].ToString();
                c.KeyNo = (db.rows[0]["keyNo"] == System.DBNull.Value) ? "" : db.rows[0]["keyNo"].ToString();
                c.LinkmanTel = (db.rows[0]["linkmanTel"] == System.DBNull.Value) ? "" : db.rows[0]["linkmanTel"].ToString();
                c.Source = (db.rows[0]["source"] == System.DBNull.Value) ? "" : db.rows[0]["source"].ToString();
                c.PocNo = (db.rows[0]["pocNo"] == System.DBNull.Value) ? "" : db.rows[0]["pocNo"].ToString();
                c.PocStatus = (db.rows[0]["pocStatus"] == System.DBNull.Value) ? "" : db.rows[0]["pocStatus"].ToString();
                c.UseStatus = (db.rows[0]["useStatus"] == System.DBNull.Value) ? "" : db.rows[0]["useStatus"].ToString();
                c.PropertyRight = (db.rows[0]["propertyRight"] == System.DBNull.Value) ? "" : db.rows[0]["propertyRight"].ToString();
                c.OwnerTel = (db.rows[0]["ownerTel"] == System.DBNull.Value) ? "" : db.rows[0]["ownerTel"].ToString();
                c.ProtectedUid = (db.rows[0]["protectedUid"] == System.DBNull.Value) ? 0 : Convert.ToInt32(db.rows[0]["protectedUid"].ToString());
                c.Eid = (db.rows[0]["eid"] == System.DBNull.Value) ? 0 : Convert.ToInt32(db.rows[0]["eid"].ToString());
                c.Ename = (db.rows[0]["ename"] == System.DBNull.Value) ? "" : db.rows[0]["ename"].ToString();
                c.Level1 = (db.rows[0]["level1"] == System.DBNull.Value) ? 0 : Convert.ToInt32(db.rows[0]["level1"].ToString());
                c.Level2 = (db.rows[0]["level2"] == System.DBNull.Value) ? 0 : Convert.ToInt32(db.rows[0]["level2"].ToString());
                c.Level3 = (db.rows[0]["level3"] == System.DBNull.Value) ? 0 : Convert.ToInt32(db.rows[0]["level3"].ToString());
                return c;
            }
        }

        public static DataTable SelectHousigById(int id)
        {
            DataSet ds = null;
            using (_Database db = new _Database("estate"))
            {
                string sql = "select * from t_housing where Id=" + id + "";
                ds = db.Query(sql);
            }

            DataTable dt = ds.Tables[0];
            return dt;
        }

        public static DataSet SelectBuildNameById(int id)
        {
            DataSet ds = null;

            using (_Database db = new _Database("estate"))
            {
                string sql = "select * from t_housing where id=" + id + "";
                ds = db.Query(sql);
            }

            return ds;
        }

        public static DataSet SelectBuildNameById(int id, string FiledList)
        {
            DataSet ds = null;

            using (_Database db = new _Database("estate"))
            {
                string sql = "select " + FiledList + " from t_housing where id=" + id + "";
                ds = db.Query(sql);
            }

            return ds;
        }

        public static DataTable p_estate_buildid(int id, _Database db)
        {
            List<SqlParameter> parms = new List<SqlParameter>();

            parms.Add(new SqlParameter("@id", id));
            SqlParameter pRetval = new SqlParameter("@retval", SqlDbType.Int, 4);
            pRetval.Direction = ParameterDirection.ReturnValue;
            parms.Add(pRetval);

            DataTable dt = null;
            dt = db.GetDataTable("p_estate_buildid", parms);

            int retval = (pRetval.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pRetval.Value);

            return dt;
        }

        public static DataSet SelectCountsByBuildIdAndRoomNo(int buildid, string roomno, int tradetype, int id, int roomid)
        {
            DataSet ds = null;

            using (_Database db = new _Database("estate"))
            {
                string sql = "select id,status from t_housing where buildId=" + buildid + " and roomNo='" + roomno + "' and roomid=" + roomid + " and tradeType=" + tradetype + " and id!=" + id + "";
                ds = db.Query(sql);
            }

            return ds;

        }

        public static DataTable p_v_LookTelLog_list_table(int page, int pagesize, string condition, ref int total, _Database db)
        {
            List<SqlParameter> parms = new List<SqlParameter>();

            parms.Add(new SqlParameter("@page", page));
            parms.Add(new SqlParameter("@pagesize", pagesize));
            parms.Add(new SqlParameter("@condition", condition));

            SqlParameter pTotal = new SqlParameter("@total", SqlDbType.Int, 4);
            pTotal.Direction = ParameterDirection.InputOutput;
            pTotal.Value = total;
            parms.Add(pTotal);
            SqlParameter pRetval = new SqlParameter("@retval", SqlDbType.Int, 4);
            pRetval.Direction = ParameterDirection.ReturnValue;
            parms.Add(pRetval);

            DataTable dt = null;
            dt = db.GetDataTable("p_v_LookTelLog_list", parms);

            total = (pTotal.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pTotal.Value);

            int retval = (pRetval.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pRetval.Value);

            return dt;
        }

        public static DataSet SelectHousing(string condition, string fileds, string orderby, _Database db)
        {
            DataSet ds = null;
            try
            {
                string sql = "select " + fileds + " from t_housing  where " + condition;
                ds = db.Query(sql);
            }
            catch { }
            finally { db.close(); }

            return ds;
        }

        public static int UpdateMark(int id, int markUid, string markUser, int markUdeptid, string markUdept, _Database db)
        {
            int rows = 0;

            try
            {
                string sql = "update t_housing set markUid=" + markUid + ",markUser='" + markUser + "',markUdeptid=" + markUdeptid + ",markUdept='" + markUdept + "',markdate='" + DateTime.Now + "' where id=" + id + "";
                rows = db.ExecuteSql(sql);
            }
            catch { }
            finally { db.close(); }

            return rows;
        }

        public static void p_housing_isupdatemark(int id, out string outmsg, _Database db)
        {
            List<SqlParameter> parms = new List<SqlParameter>();

            parms.Add(new SqlParameter("@id", id));

            SqlParameter pOutmsg = new SqlParameter("@outmsg", SqlDbType.VarChar, 256);
            pOutmsg.Direction = ParameterDirection.Output;
            parms.Add(pOutmsg);
            SqlParameter pRetval = new SqlParameter("@retval", SqlDbType.Int, 4);
            pRetval.Direction = ParameterDirection.ReturnValue;
            parms.Add(pRetval);


            db.execute_procedure("p_housing_isupdatemark", parms);

            outmsg = (pOutmsg.Value == System.DBNull.Value) ? "" : Convert.ToString(pOutmsg.Value);

            int retval = (pRetval.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pRetval.Value);
        }

        public static DataTable p_housing_Recycle_list_table(int page, int pagesize, string condition, ref int total, _Database db)
        {
            List<SqlParameter> parms = new List<SqlParameter>();

            parms.Add(new SqlParameter("@page", page));
            parms.Add(new SqlParameter("@pagesize", pagesize));
            parms.Add(new SqlParameter("@condition", condition));

            SqlParameter pTotal = new SqlParameter("@total", SqlDbType.Int, 4);
            pTotal.Direction = ParameterDirection.InputOutput;
            pTotal.Value = total;
            parms.Add(pTotal);
            SqlParameter pRetval = new SqlParameter("@retval", SqlDbType.Int, 4);
            pRetval.Direction = ParameterDirection.ReturnValue;
            parms.Add(pRetval);

            DataTable dt = null;
            dt = db.GetDataTable("p_housing_Recycle_list", parms);

            total = (pTotal.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pTotal.Value);

            int retval = (pRetval.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pRetval.Value);

            return dt;
        }
        public static int UpdateFileds(DataSet ds, int Id, int did, string dname, int aid, string area, string buildname,
    int buildid, string roomno, int roomid, int tradetype, int room, int hall, int restroom,
    int balcony, int kitchen, float acreage, float totalprice, float leaseprice, float unitprice, string owner, string ownertel, string linkman, string linkmantel, float costprice, string source, string pocno,
    string pocstatus, int redeem, string usestatus, string propertyright, string direct, string fitment, string housestyle, int use,
    string housetype, int haskey, string keyno, string keepkey, string keepkeydate, int ctycont, string ctycontuser, string ctycontdate,
    int isonly, string onlyuser, string onlydate, int markuid, string markuser,
    int markudeptid, string markudept, string remark, string tenant, float assignmentfee, int adduid, string adduser, string addudept, int addudeptid, string addubank, int Protected,
    int clickcount, string happliances, string mark1, string mark2, string mark3, string mark4, string mark5, int status,
    int status1, string modifyuser, int dltflag, int isvalid, int recommend, int hasphoto, string endDate, string ctyEndDate, _Database db)
        {
            string[] ValueList = { "'" + Id + "'", "'" + did + "'", "'" + dname + "'", "'" + aid + "'", "'" + area + "'", "'" + buildname + "'",
                                     "'" + buildid + "'", "'" + roomno + "'", "'" + roomid + "'", "'" + tradetype + "'", "'" + room + "'", "'" + hall + "'", 
                                     "'" + restroom + "'", "'" + balcony + "'", "'" + kitchen + "'", "" + acreage + "", "" + totalprice + "", "" + leaseprice + "", "" + unitprice + "", 
                                     "'" + owner + "'", "'" + ownertel + "'", "'" + linkman + "'", "'" + linkmantel + "'", "'" + costprice + "'", "'" + source + "'", "'" + pocno + "'",                                   
                                     "'" + pocstatus + "'", "'" + redeem + "'", "'" + usestatus + "'", "'" + propertyright + "'", "'" + direct + "'", "'" + fitment + "'", "'" + housestyle + "'", 
                                     "'" + use + "'", "'" + housetype + "'", "'" + haskey + "'", "'" + keyno + "'", "'" + keepkey + "'", "'" + keepkeydate + "'", "'" + ctycont + "'", "'" + ctycontuser + "'", "'" + ctycontdate + "'",
                                     "'" + isonly + "'","'"+onlyuser+"'", "'" + onlydate + "'", "'" + markuid + "'", "'" + markuser + "'",
                                     "'" + markudeptid + "'", "'" + markudept + "'", "'" + remark + "'", "'" + tenant + "'", "'" + assignmentfee + "'", "'" + adduid + "'", "'" + adduser + "'", "'" + addudept + "'", "'" + addudeptid + "'", "'" + addubank + "'", "'" + Protected + "'",
                                   "'" + clickcount + "'", "'" + happliances + "'", "'" + mark1 + "'", "'" + mark2 + "'", "'" + mark3 + "'", "'" + mark4 + "'", "'" + mark5 + "'", "'" + status + "'", 
                                   "'" + status1 + "'", "'" + modifyuser + "'", "'" + dltflag + "'", "'" + isvalid + "'", "'" + recommend + "'", "'" + hasphoto + "'","''","''","''","''","''","'"+endDate+"'","'"+ctycontdate+"'"
                                 };
            int rows = 0;
            try
            {
                StringBuilder sb = new StringBuilder();
                sb.Append(" update t_housing set ");
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    sb.Append(ds.Tables[0].Rows[i]["cName"] + "=");
                    int index = _Convert.ToInt(ds.Tables[0].Rows[i]["corder"].ToString()) - 1;
                    sb.Append(ValueList[index]);
                    if (i < ds.Tables[0].Rows.Count - 1)
                        sb.Append(",");

                }

                sb.Append(" where id='" + Id + "'");

                rows = db.ExecuteSql(sb.ToString());
            }
            catch { }
            finally
            {
                db.close();
            }
            return rows;
        }

        public static int UpdateKeys(int id, int hasKey, string keyNo, string keepKey, string keepKeyDate, int ctyCont, string ctyContUser, string ctyContDate, int isOnly, string OnlyUser, string OnlyDate, _Database db)
        {
            int rows = 0;
            try
            {
                string sql = "update t_housing set hasKey=" + hasKey + ",keyNo='" + keyNo + "',keepKey='" + keepKey + "',keepKeyDate='" + keepKeyDate + "',ctyCont=" + ctyCont + ",ctyContUser='" + ctyContUser + "',ctyContDate='" + ctyContDate + "',isOnly=" + isOnly + ",OnlyUser='" + OnlyUser + "',OnlyDate='" + OnlyDate + "' where Id=" + id + "";
                rows = db.ExecuteSql(sql);
            }
            catch { }
            finally
            {
                db.close();
            }
            return rows;
        }

        public static int UpdateKeys(int id, int hasKey, string keyNo, string keepKey, string keepKeyDate, _Database db)
        {
            int rows = 0;
            try
            {
                string sql = "update t_housing set hasKey=" + hasKey + ",keyNo='" + keyNo + "',keepKey='" + keepKey + "',keepKeyDate='" + keepKeyDate + "' where Id=" + id + "";
                rows = db.ExecuteSql(sql);
            }
            catch { }
            finally
            {
                db.close();
            }
            return rows;
        }

        public static void UpdateTotalPrice(int TradeType, string price, string unitprice, int id, _Database db)
        {

            try
            {
                string sql = "";



                if (TradeType == 2)
                {
                    sql = "update t_housing set Leaseprice='" + unitprice + "',unitprice=(" + unitprice + "/acreage) where Id=" + id + "";

                }
                else if (TradeType == 1)
                {
                    sql = "update t_housing set totalprice='" + price + "',unitprice=(" + price + "/acreage*10000) where Id=" + id + "";

                }
                else if (_Convert.ToInt(price, 0) > 0 && _Convert.ToInt(unitprice, 0) > 0)
                {
                    sql = "update t_housing set totalprice='" + price + "',Leaseprice='" + unitprice + "',unitprice=(" + price + "/acreage*10000) where Id=" + id + "";
                }

                db.ExecuteSql(sql);
            }
            catch
            {

            }
            finally
            {
                db.close();
            }
        }

        public static int DeleteHousing(int id, _Database db)
        {
            int rows = 0;

            try
            {
                string sql = "delete from t_housing where  id =" + id + " and dltflag=1 ";
                rows = db.ExecuteSql(sql);

            }
            catch { }
            finally { db.close(); }

            return rows;
        }

        public static int UpdateDltFlag(int id, _Database db)
        {
            int rows = 0;

            try
            {
                string sql = "update  t_housing set dltflag=0 where  id =" + id + " and dltflag=1 ";
                rows = db.ExecuteSql(sql);
            }
            catch { }
            finally { db.close(); }

            return rows;
        }

        public static DataTable p_transaction_detail_table(int pty_id, int kty_id, _Database db)
        {
            List<SqlParameter> parms = new List<SqlParameter>();

            parms.Add(new SqlParameter("@pty_id", pty_id));
            parms.Add(new SqlParameter("@kty_id", kty_id));
            SqlParameter pRetval = new SqlParameter("@retval", SqlDbType.Int, 4);
            pRetval.Direction = ParameterDirection.ReturnValue;
            parms.Add(pRetval);

            DataTable dt = null;
            dt = db.GetDataTable("p_transaction_detail", parms);

            int retval = (pRetval.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pRetval.Value);

            return dt;
        }

        public static string TranToDeal(int Housingid, string DealPrice, string DealDate, string sInfo, _Database db)
        {
            string outmsg = "";

            try
            {
                string sSql = "begin tran;";
                if (DealPrice != "")
                {
                    sSql += "declare @acreage numeric(12,2);";
                    sSql += "update t_housing set status=2,status1 = 1,ProtectedDate=GETDATE(),protectedUid=" + _Utility.GetInfoBySession().m_sUserID + ",protectedUser='" + _Utility.GetInfoBySession().m_sUserName + "',protectedUDeptId=" + _Utility.GetInfoBySession().m_sDeptID + ",protectedUDept='" + _Utility.GetInfoBySession().m_sDeptName + "' where id = " + Housingid + ";";
                    sSql += "set @acreage = 0;select @acreage = acreage from t_housing where id = " + Housingid + ";";
                    sSql += "update t_housing set totalprice = " + DealPrice + " where id = " + Housingid + ";";
                    sSql += "insert into t_opr_dealPrice (housingid,userid,acreage,ttl_price,dealdate) values(" + Housingid + "," + _Utility.GetInfoBySession().m_sUserID + ",@acreage," + DealPrice + ",'" + DealDate + "');";
                    sSql += "if @acreage <> 0 begin update t_housing set unitprice = case tradetype when '1' then isnull(totalprice,0)*10000 else isnull(totalprice,0) end / @acreage where id = " + Housingid + ";end;";
                }
                sSql += "if @@error <> 0 begin rollback;RAISERROR ('数据库错误.', 16, 1) end else begin commit; end;";

                int rows = db.ExecuteSql(sSql);
                if (rows > 0)
                {
                    outmsg = "操作成功";
                }
                else
                {
                    outmsg = "操作失败";
                }
            }
            catch { }
            finally { db.close(); }

            return outmsg;
        }

        public static string UpdateStatus1(int housingID, _Database db)
        {
            string IdList = ZLDC.Common.BaseUser.UserInfo.getAllChildDept(_Utility.GetInfoBySession().m_sDeptID, _Utility.GetInfoBySession().m_sEHRUserID);
            string sql = "update t_housing set status1 = 0 where id = " + housingID + "  ";
            if (!ZLDC.Common.BaseUser.UserInfo.GetPopedom("转所有成交盘"))
                sql += "and markUDeptId in(" + IdList + ")";

            string outmsg = "取消失败";
            try
            {
                int rows = db.ExecuteSql(sql);

                if (rows > 0)
                {
                    outmsg = "取消成功";
                }
            }
            catch { }
            finally { db.close(); }

            return outmsg;
        }

        public static void UpdateMark5(int housingId, string importContent, _Database db)
        {
            try
            {
                string sql = " update t_housing set mark5='" + importContent + "' where id=" + housingId + "";
                db.ExecuteSql(sql);
            }
            catch
            {

            }
            finally
            {
                db.close();
            }
        }
        public static void UpdateMark4(int housingId, string importContent, _Database db)
        {
            try
            {
                string sql = " update t_housing set mark4='" + importContent + "',uddate=getDate() where id=" + housingId + "";

                db.ExecuteSql(sql);
            }
            catch
            {

            }
            finally
            {
                db.close();
            }
        }

        public static DataTable ShowOtherHousing(int id, _Database db)
        {
            DataSet ds = null;
            DataTable dt = null;
            try
            {
                string sql = "select * from t_housing where dltflag = 0 and ownerTel = (select ownerTel from t_housing where Id = " + id + ") and Id != " + id;
                ds = db.Query(sql);

                if (ds != null && ds.Tables.Count > 0)
                    dt = ds.Tables[0];
            }
            catch { }
            finally { db.close(); }

            return dt;
        }


        public static DataTable P_t_keyorExclusive_list(string userid, _Database db)
        {
            DataSet ds = null;
            DataTable dt = null;

            try
            {
                string sql = " select * from t_keyorExclusive where userid=" + userid + "";
                ds = db.Query(sql);
                if (ds != null && ds.Tables.Count > 0)
                    dt = ds.Tables[0];
            }
            catch
            {

            }
            finally
            {
                db.close();
            }
            return dt;
        }
        public static void KeyorExclusiveSave(int hid, int userid, string username, int type, _Database db)
        {
            try
            {
                string sql = " insert into t_keyorExclusive values(" + hid + "," + userid + ",'" + username + "'," + type + ",getDate())";
                db.Query(sql);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                db.close();
            }

        }

        public static DataTable p_CommendHousing_list(_Database db)
        {
            DataSet ds = null;
            DataTable dt = null;

            try
            {
                string sql = " select * from t_housingEveryday where DATEDIFF(DD,addDate,GETDATE())>=5  and  userid=" + _Utility.GetInfoBySession().m_sUserID + "";
                ds = db.Query(sql);
                if (ds != null && ds.Tables.Count > 0)
                    dt = ds.Tables[0];
            }
            catch
            {

            }
            finally
            {
                db.close();
            }
            return dt;
        }
    }
}
